package cn.just.dao;

import cn.just.entity.Tag;
import cn.just.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wujiaming
 */
public class TagDao {

    /**
     * 根据文章id查询对应的标签
     * @param oId
     * @return
     * @throws SQLException
     */
    public List<Tag> findTagByArticleId(int oId) throws SQLException {

        List<Tag> tags = new ArrayList<Tag>();
        Connection connect = JDBCUtil.getConnect();
        String sql= "SELECT t.title " +
                "FROM tag t JOIN tag_article ta " +
                "ON t.oId = ta.tag_oId " +
                "WHERE ta.article_oId=?";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        preparedStatement.setInt(1,oId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            String title = resultSet.getString("title");
            Tag tag = new Tag(null,title,null);
            tags.add(tag);
        }
        connect.close();
        return tags;
    }

    /**
     * 查询 标签关联的文章计数 最多的前五条标签
     * @return
     * @throws SQLException
     */
    public List<Tag> findTagByreferenceCount() throws SQLException {
        ArrayList<Tag> tags = new ArrayList<Tag>();
        Connection connect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        connect = JDBCUtil.getConnect();
        String sql = "select t.oId,t.title,t.referenceCount " +
                "from tag t " +
                "order by referenceCount desc " +
                "limit 0,5";
        preparedStatement = connect.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            String oId = resultSet.getString("oId");
            String title = resultSet.getString("title");
            String referenceCount = resultSet.getString("referenceCount");
            Tag tag = new Tag(Integer.parseInt(oId),title,Integer.parseInt(referenceCount));
            tags.add(tag);
        }

        JDBCUtil.close(connect,preparedStatement,resultSet);
        return tags;
    }
}
